package com.sxj.corejava.code02_jdbc;

import com.sxj.corejava.entity.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 石小俊
 * @date 2023年11月2023/11/30日 19:07
 * 创建一张学生表,拥有以下字段:主键id、学号no唯一且不为空、姓名不为空、年龄
 * 完成以下方法
 * 1.添加学生信息
 * 2.根据学号删除指定学生信息
 * 3.根据学号修改指定学生信息
 * 4.查询所有学生信息
 * 5.根据学号查询指定学生信息
 * 6.根据姓名进行模糊查询
 * 7.根据年龄范围查询指定学生信息
 */
public class Test05_学生管理系统 {

    public static void main(String[] args) {
//        Student student = new Student("202200007","alice",18);
//        insertStudent(student);

//        deleteByNo("202200004");

//        Student student = new Student("202200008","李白",18);
//        updateStudent(student);

//        List<Student> students = selectAll();
//        for(Student student : students){
//            System.out.println(student);
//        }

//        Student student = selectByNo("202200001");
//        System.out.println(student);

//        List<Student> students = selectByName("李");
//        for(Student student : students){
//            System.out.println(student);
//        }

        List<Student> students = selectByAge(19,21);
        for(Student student : students){
            System.out.println(student);
        }
    }

    public static void insertStudent(Student student){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            String sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student ")
                    .append("   (no,name,age) ")
                    .append(" values ")
                    .append("   (?,?,?)")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1,student.getNo());
            ps.setString(2,student.getName());
            ps.setInt(3,student.getAge());

            ps.executeUpdate();
            System.out.println("恭喜您,添加成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void deleteByNo(String no){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            String sql = new StringBuffer()
                    .append(" delete from t_student ")
                    .append(" where no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1,no);

            ps.executeUpdate();
            System.out.println("删除成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void updateStudent(Student student){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            String sql = new StringBuffer()
                    .append(" update t_student ")
                    .append(" set ")
                    .append("   name = ?, ")
                    .append("   age = ? ")
                    .append(" where no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1,student.getName());
            ps.setInt(2,student.getAge());
            ps.setString(3,student.getNo());
            ps.executeUpdate();
            System.out.println("修改成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static List<Student> selectAll(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            String sql = new StringBuffer()
                    .append(" select id,no,name,age")
                    .append(" from t_student ")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return students;
    }

    public static Student selectByNo(String no){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student student = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1,no);
            rs = ps.executeQuery();
            while (rs.next()){
                student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return student;
    }

    public static List<Student> selectByName(String name){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where name like ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1,"%"+name+"%");
            rs = ps.executeQuery();
            while (rs.next()){
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return students;
    }

    public static List<Student> selectByAge(Integer minAge,Integer maxAge){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            String sql = new StringBuffer()
                    .append(" select id,no,name,age ")
                    .append(" from t_student ")
                    .append(" where age between ? and ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,minAge);
            ps.setInt(2,maxAge);
            rs = ps.executeQuery();
            while (rs.next()){
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                students.add(student);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return students;
    }
}
